#! /usr/bin/env python3
# -*- coding: utf-8 -*-
#######################
#	获取新浪网站企业财报信息--季度财报
#######################
import sys
import time
import urllib
import re
from bs4 import BeautifulSoup
import io
import os
import math
dirs = os.path.abspath(os.path.dirname(__file__)+"/../Config")
os.sys.path.append(dirs)   #将上上级目录加载到python的环境变量中
from config import conn,driver,logpath
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),'Began to pull data...')

cur = conn.cursor()
currentyear = time.strftime("%Y", time.localtime())
years = [int(currentyear),int(currentyear)-1,int(currentyear)-2,int(currentyear)-3]
#years = [int(currentyear)]

print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"open Browser ...")
#driver = webdriver.Firefox()
#driver = webdriver.PhantomJS(executable_path='D:/phantomjs198/phantomjs.exe')
#driver = webdriver.PhantomJS(executable_path='D:/phantomjs198/phantomjs.exe')
#driver = webdriver.PhantomJS(executable_path='/usr/local/phantomjs')
#driver = connect(webdriver.DesiredCapabilities.HTMLUNIT, server="http://localhost:4444")
#driver = webdriver.Remote(desired_capabilities=webdriver.DesiredCapabilities.HTMLUNIT)
#driver = webdriver.Remote("http://localhost:4444/wd/hub", webdriver.DesiredCapabilities.HTMLUNIT.copy())
#driver = webdriver.Remote(desired_capabilities=DesiredCapabilities.HTMLUNIT)     #（2）
time.sleep(5)

#获取远程URL链接
def GetUrlData(url):
	data=urllib.request.urlopen(url).read()
	return data

#递归获取页面内容 如果页面载入失败 重新加载
def DriverGet(url,tableid,eid,enterprise,times=1):
	js='window.open("%s")' % url
	driver.execute_script(js)
	handles = driver.window_handles # 获取当前窗口句柄集合（列表类型）
	driver.switch_to_window(handles[-1])
	# driver.get(url)
	driver.implicitly_wait(10)
	time.sleep(10)
	soup = None
	soup = BeautifulSoup(driver.page_source,"html.parser")
	if(soup.find("table",id=tableid) or soup.find("div",id=tableid)):
		return soup
	else:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Reload the page failed to open the page ",times,"time...")		
		if(times < 5):
			times = times+1
			return DriverGet(url,tableid,eid,enterprise,times)
		else:
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"The current enterprise information fetching failure...")
			#保存抓取失败企业
			insertsql = "insert into crawlerfalselog (eid,ename,url,type) values (%s,'%s','%s',2)" % (eid,enterprise,url)
			try:
				cur.execute(insertsql)
				conn.commit()
			except Exception as e:
				print("Data add failure ")
			driver.close()
			driver.quit()
			exit()

#获取详请页面内容 处理
def dealData(searchname):
	print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"get Sina company code")
	url = "http://suggest3.sinajs.cn/suggest/type=&key=%s" % urllib.request.quote(searchname)
	geturlData = GetUrlData(url).decode("gbk")
	enterprisecode = geturlData.split("=")[1].split(",")[2]
	enterprisestockcode = geturlData.split("=")[1].split(",")[3]

	#查询公司对应ID
	sqls = "select eid from enterpriseinfo where searchname like '%s'" % ("%"+searchname+"%")
	try:
		cur.execute(sqls)
		result = cur.fetchone()
	except Exception as e:
		print("Data add failure ")

	eid = result[0]

	#查询公司在本行业内的排名
	print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the HomePage ")
	soup = DriverGet("http://finance.sina.com.cn/realstock/company/%s/nc.shtml" % enterprisestockcode,"rankSummary_l",eid=eid,enterprise=searchname)
	sy = soup.find("div",id="rankSummary_l").find_all("span")
	sylist = sy[2].text.strip()
	pattern = re.compile("第(\d*)\((\d*)家\)")
	res = pattern.search(sylist).groups()
	rankSummary = "%s/%s" % (res[0],res[1])
	##########################

	for year in years:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"get ",year," year data")

		##########################################################财务摘要
		"""
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"打开 【",searchname,"】 公司财务摘要页面")
		driver.get("http://vip.stock.finance.sina.com.cn/corp/go.php/vFD_FinanceSummary/stockid/%s/displaytype/4.phtml" % enterprisecode)
		driver.implicitly_wait(10)
		time.sleep(5)
		soup = BeautifulSoup(driver.page_source,"html.parser")
		cwzy_trlist = soup.find("table",id="FundHoldSharesTable").find_all("tr")
		#去掉表头
		cwzy={}
		for cwzy_tr in cwzy_trlist[1:]:
			cwzy_tdlist = cwzy_tr.find_all("td")
			if(len(cwzy_tdlist)>=2):
				key = cwzy_tdlist[0]
				value = cwzy_tdlist[1]
				cwzy[key.text.strip()] = value.text.strip()
		"""

		#############################################################财务指标
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open The company's financial index page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_FinancialGuideLine/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"BalanceSheetNewTable0",eid=eid,enterprise=searchname)
		cwzb_trlist = soup.find("table",id="BalanceSheetNewTable0").find_all("tr")
		#去掉表头
		cwzb={}
		for cwzb_tr in cwzb_trlist[1:]:
			items={}
			cwzb_tdlist = cwzb_tr.find_all("td")
			if(len(cwzb_tdlist)>=2):
				for i in range(1,len(cwzb_tdlist)):
					items[len(cwzb_tdlist)-i] = cwzb_tdlist[i].text.strip().replace(',','').replace('--','0')
				key = cwzb_tdlist[0]
				cwzb[key.text.strip()] = items
		#print(cwzb)
		#print(driver.window_handles)

		#############################################################资产负债表
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"open The company's balance sheet page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"BalanceSheetNewTable0",eid=eid,enterprise=searchname)
		zcfz_trlist = soup.find("table",id="BalanceSheetNewTable0").find_all("tr")
		#去掉表头
		zcfz={}
		for zcfz_tr in zcfz_trlist[1:]:
			items={}
			zcfz_tdlist = zcfz_tr.find_all("td")
			if(len(zcfz_tdlist)>=2):
				for i in range(1,len(zcfz_tdlist)):
					items[len(zcfz_tdlist)-i] = zcfz_tdlist[i].text.strip().replace(',','').replace('--','0')
				key = zcfz_tdlist[0]
				zcfz[key.text.strip()] = items
		#print(zcfz)
		#print(driver.window_handles)

		#############################################################利润表
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Opens the page of the company profit")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_ProfitStatement/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"ProfitStatementNewTable0",eid=eid,enterprise=searchname)
		lr_trlist = soup.find("table",id="ProfitStatementNewTable0").find_all("tr")
		#去掉表头
		lr={}
		for lr_tr in lr_trlist[1:]:
			items={}
			lr_tdlist = lr_tr.find_all("td")
			if(len(lr_tdlist)>=2):
				for i in range(1,len(lr_tdlist)):
					items[len(lr_tdlist)-i] = lr_tdlist[i].text.strip().replace(',','').replace('--','0')
				key = lr_tdlist[0]
				lr[key.text.strip()] = items
		#print(lr)
		#print(driver.window_handles)

		#############################################################现金流量表
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company's cash flow statement page")
		soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_CashFlow/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"ProfitStatementNewTable0",eid=eid,enterprise=searchname)
		xjll_trlist = soup.find("table",id="ProfitStatementNewTable0").find_all("tr")
		#去掉表头
		xjll={}
		for xjll_tr in xjll_trlist[1:]:
			items={}
			xjll_tdlist = xjll_tr.find_all("td")
			if(len(xjll_tdlist)>=2):
				for i in range(1,len(xjll_tdlist)):
					items[len(xjll_tdlist)-i] = xjll_tdlist[i].text.strip().replace(',','').replace('--','0')
				key = xjll_tdlist[0]
				xjll[key.text.strip()] = items
		#print(xjll)
		#print(driver.window_handles)
		zcfz["dqgxzw"] = {}
		lr['elxbzbs'] = {}
		xjll['kzyzpxjll'] = {}
		zcfz['ybxzchj'] = {}
		xjll['kzyzpxjlllxbzbs'] = {}
		xjll['kzyzpxjldqgxzwb'] = {}
		zcfz["gxzw"] = {}
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Data mining...")
		for quarter in zcfz["短期借款"]:	
			zcfz["dqgxzw"][quarter] = float(zcfz["短期借款"][quarter])+float(zcfz["交易性金融负债"][quarter])+float(zcfz["应付票据"][quarter])+float(zcfz["一年内到期的非流动负债"][quarter])+float(zcfz["应付短期债券"][quarter])
			if(zcfz["dqgxzw"][quarter] == 0):
				xjll['kzyzpxjldqgxzwb'][quarter] = 0
			else:
				xjll['kzyzpxjldqgxzwb'][quarter] = format((float(xjll["经营活动产生的现金流量净额"][quarter])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"][quarter]))/zcfz["dqgxzw"][quarter],'.2f')
			if(lr["利息支出"][quarter] == "0"):				
				lr['elxbzbs'][quarter] = '0'
				xjll['kzyzpxjlllxbzbs'][quarter] = 0
			else:
				xjll['kzyzpxjlllxbzbs'][quarter] = format((float(xjll["经营活动产生的现金流量净额"][quarter])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"][quarter]))/float(lr["利息支出"][quarter]),'.2f')
				lr['elxbzbs'][quarter] = format(float(lr["三、营业利润"][quarter])/float(lr["利息支出"][quarter]), '.2f')
			zcfz['ybxzchj'][quarter] = format(float(zcfz["流动资产合计"][quarter])/float(zcfz["资产总计"][quarter])*100, '.2f')
			xjll['kzyzpxjll'][quarter] = format(float(xjll["经营活动产生的现金流量净额"][quarter])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"][quarter]), '.2f')
			#添加 行业排名 cwzb["存货周转率(次)"] xjll['经营活动现金流入小计'] cwzb['净资产收益率']
			zcfz["gxzw"][quarter] = zcfz["dqgxzw"][quarter] + float(zcfz["长期借款"][quarter]) + float(zcfz["应付债券"][quarter])


			sql = "replace into enterprisefinance_quarter (`eid`,`ename`,`year`,`quarter`,`cutoffdate`,`gdzc`,`jyxjrzc`,`hbzj`,`qtzc`,`ybxzchj`,`zzc`,`dqjk`,`yfpj`,`ynndqdfldfz`,`ldfzhj`,`cqjk`,`fldfzhj`,`fzhj`,`sszb`,`syzjchj`,`syl`,`yysr`,`qjfy`,`jlr`,`jyhdcsdxjllje`,`gjcqzjszfdxj`,`czcqzjszfdxj`,`kzyzpxjll`,`czhdjxjl`,`EBIT`,`dqgxzw`,`gxzw`,`mll`,`zcfzl`,`kzyzpxjlllxbzbs`,`kzyzpxjldqgxzwb`,`elxbzbs`,`xjhll`,`dbbl`,`chzzl`,`jyhdxjlrxj`,`jzcsyl`,`hypm`) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (eid,searchname,year,quarter,lr["报表日期"][quarter],zcfz["非流动资产合计"][quarter],zcfz["交易性金融资产"][quarter],zcfz["货币资金"][quarter],"0",zcfz['ybxzchj'][quarter],zcfz["资产总计"][quarter],zcfz["短期借款"][quarter],zcfz["应付票据"][quarter],zcfz["一年内到期的非流动负债"][quarter],zcfz["流动负债合计"][quarter],zcfz["长期借款"][quarter],zcfz["非流动负债合计"][quarter],zcfz["负债合计"][quarter],zcfz["实收资本(或股本)"][quarter],zcfz["所有者权益(或股东权益)合计"][quarter],"0",lr["营业收入"][quarter],lr["二、营业总成本"][quarter],lr["四、净利润"][quarter],xjll["经营活动产生的现金流量净额"][quarter],xjll["购建固定资产、无形资产和其他长期资产所支付的现金"][quarter],xjll["处置固定资产、无形资产和其他长期资产所收回的现金净额"][quarter],xjll['kzyzpxjll'][quarter],xjll["筹资活动产生的现金流量净额"][quarter],lr["利润总额"][quarter],zcfz['dqgxzw'][quarter],zcfz["gxzw"][quarter],cwzb["营业利润率(%)"][quarter],cwzb["资产负债率(%)"][quarter],xjll['kzyzpxjlllxbzbs'][quarter],xjll['kzyzpxjldqgxzwb'][quarter],lr['elxbzbs'][quarter],"0","0",cwzb["存货周转率(次)"][quarter],xjll['经营活动现金流入小计'][quarter],cwzb['净资产收益率(%)'][quarter],rankSummary)

			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Corporate earnings data warehousing...")
			exestatus = "[SUCCESS]:"
			try:
				cur.execute(sql)
				conn.commit()
			except Exception as e:
				print("Data add failure ")
				exestatus = "[ERROR]:"

			# file_object = open(logpath+"/finance_quarter.log", 'a')
			# file_object.write(exestatus+"	"+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())+"	"+sql+"\r\n")
			# file_object.close()

			# #添加只取最近一条 
			# exit()
			#########
		#########数据处理完

print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Start to get the company data...")
#企业列表
if "php" in sys.argv:
	enterprise = urllib.request.unquote(sys.argv[1])
else:
	enterprise = sys.argv[1]
dealData(enterprise)
driver.close()
driver.quit()
